package org.languageguide.readings;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.languageguide.storage.DBConnector;
import org.owasp.esapi.ESAPI;

public class DefinitionManager {

	
	
	public static List<Definition> loadDefinitionTranslations(Integer readingId, String target){
		List<Definition> definitions = new ArrayList<Definition>();
		
//		String sql_load = "SELECT * FROM ReadingSentences LEFT JOIN ReadingSentenceTranslations ON ReadingSentences.Id = ReadingSentenceTranslations.ReadingSentencesId AND ReadingSentenceTranslations.TargetLanguage = ? WHERE ReadingSentences.ReadingId = ? ORDER BY Paragraph, SentenceOrder";
		String sql_load  = "SELECT * FROM ReadingDefinitions LEFT JOIN ReadingDefinitionTranslation ON ReadingDefinitions.Id = ReadingDefinitionTranslation.DefinitionId AND TargetLanguage = ? And ReadingDefinitions.SortingOrder = ReadingDefinitionTranslation.SortingOrder WHERE ReadingId = ? Order By ReadingDefinitions.Id, ReadingDefinitions.SortingOrder";
		//LEFT JOIN Users2 ON Translated.UserId = Users2.userid 
		//System.out.println("sql_load:" + sql_load);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			
			PreparedStatement stmt = connection.prepareStatement(sql_load);
			stmt.setString(1, target);
			stmt.setInt(2, readingId);
			ResultSet set = stmt.executeQuery();
			while (set.next()){
				Definition def = new Definition(set);
				definitions.add(def);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			//if (connector != null){
				connector.close(connection);
			//}
		}
		return definitions;
	}

	public void saveDefinitions(HttpSession session, HttpServletResponse response, HttpServletRequest request) throws IOException{
		org.languageguide.user.User user = org.languageguide.user.UserManager.getUser(session);
		if (user == null){
			user =	org.languageguide.user.UserManager.loginFromCookie(request);
			if (user == null){
				response.sendRedirect("/user/login.jsp");
				return;
			}
		}
		String submit = request.getParameter("translation.submit");
		if (submit == null){
			submit = "Save";
		}
	
	
		request.setCharacterEncoding("UTF-8");
	
	
		String target = request.getParameter("target");
		//org.languageguide.translation.Category cat = categoryManager.getCategory(category, num, language, target);
		if (submit.equals("Cancel")){
			//cat.finishEdit();
			//user.unlockCategories();
			//
			//redirect(categoryManager, response, request, category, num, language, target, section);
			response.sendRedirect(".?language=en&target=" + target);
			return;
	
		}
	
	//readingsManager.saveTranslations(session, request, user);
			//String totalStr  = request.getParameter("row_count");
			//System.out.println("totalStr" + totalStr);
			//int total = Integer.parseInt(totalStr);
			String readingId = request.getParameter("readingId");
			
			if (readingId == null || target == null){
				System.out.println("error");
				//return ;
			}
			String message = null;
		
			org.languageguide.storage.DBConnector connector = org.languageguide.storage.DBConnector.getInstance();
			java.sql.Connection connection = connector.getConnection();	
			
			//Map<Integer, Integer> translationIdMap = new TreeMap<Integer, Integer>();
			//List<Integer> defIds = new ArrayList<Integer>();
			//Map<Integer, String> translationMap = new TreeMap<Integer, String>();
			List<Definition> definitions = new ArrayList<Definition>();
			try{
				
				//String getReadingSql = "SELECT Id, SentenceOrder,  LEFT JOIN ReadingSentenceTranslations ON ReadingSentenceTranslations.ReadingSentencesId = ReadingSentences.Id FROM ReadingSentences where ReadingId = ?";	
				String getReadingSql = "SELECT ReadingDefinitions.Id, ReadingDefinitions.SortingOrder, ReadingDefinitionTranslation.Body FROM ReadingDefinitions LEFT JOIN ReadingDefinitionTranslation ON ReadingDefinitionTranslation.DefinitionId = ReadingDefinitions.Id AND ReadingDefinitionTranslation.TargetLanguage = ? WHERE ReadingId = ? ORDER BY ReadingDefinitions.Id, ReadingDefinitions.SortingOrder";
				PreparedStatement stmt = connection.prepareStatement(getReadingSql); // ReadingDefinitions.SentenceOrder, 
				stmt.setString(1, target);
				stmt.setString(2, readingId);
				ResultSet rs = stmt.executeQuery();
				while (rs.next()){
					Integer translationId = rs.getInt(1);
					Integer order = rs.getInt(2);
					//Integer translationId = rs.getInt(1);
					//defIds.add(translationId);
					//Integer sentenceOrder = rs.getInt(2);
					String translation = rs.getString(3);
					Definition definition = new Definition(translationId, order, translation);
					definitions.add(definition);
					//translationIdMap.put(sentenceOrder, translationId);
					//translationMap.put(translationId, translation);
				}
				
				String sql_save = "INSERT INTO ReadingDefinitionTranslation (DefinitionId, SortingOrder, TargetLanguage, Body) Values (?, ?, ?, ?)";
				String sql_update = "UPDATE ReadingDefinitionTranslation Set Body = ? WHERE DefinitionId = ? AND SortingOrder = ? AND TargetLanguage  = ?";
				connection.setAutoCommit(false);
				 stmt = connection.prepareStatement(sql_save);
				 PreparedStatement stmtUpdate = connection.prepareStatement(sql_update);
				//String ipAddress = request.getRemoteAddr();
				 
				 
				 
				 
				for (Definition definition: definitions){
					String param = request.getParameter(definition.getKey());
					System.out.println("key" + definition.getKey());
					if (param != null){
						param = ESAPI.encoder().encodeForHTML(param);
						//Integer paramInt = Integer.parseInt(param);
						//Integer readingSentenceId = translationIdMap.get(id);
						String translation = definition.getTranslationBodyPure();
						System.out.println("translation" + translation + "-" + param);
						if (translation == null && !param.equals("")){
							stmt.setInt(1, definition.getId());
							stmt.setInt(2, definition.getOrder());
							stmt.setString(3, target);
							stmt.setString(4, param);
							stmt.addBatch();
						}
						else if (!param.equals(translation)){
							System.out.println("update" + param + "-" + translation);
							stmtUpdate.setString(1, param);
							stmtUpdate.setInt(2, definition.getId());
							stmtUpdate.setInt(3, definition.getOrder());
							stmtUpdate.setString(4, target);						
							stmtUpdate.addBatch();
						}
						//reading.saveSentenceTranslation(connection, parameterId, param, user, ipAddress);				
					}
						//String approvalvalue = request.getParameter("approval" + parameterId);
	
						//System.out.println("param:" + param);			
						//if (param.indexOf("&#x3f;") == -1) // some browsers are putting weird stuff in translation
	
	
	
						//			parameterId++;
					//		else{
					//		parametersExist = false;
					//}
				}
				stmt.executeBatch();
				stmtUpdate.executeBatch();
				connection.commit();
				//cat.prepare();
				//cat.finishEdit();
			//	user.unlockCategories();
				
				ApprovalUtil.setApproval(request, connection, readingId, target, "ApprovedDefinitions");
				
				
				session.setAttribute("message", "Thank you! Your translations have been saved.");
				response.sendRedirect(".?language=en&target=" + target);
				//redirect(categoryManager, response, request, category, num, language, target, section);
			}
			catch (java.sql.SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
				final java.io.Writer result = new java.io.StringWriter();
			    final java.io.PrintWriter printWriter = new java.io.PrintWriter(result);
		    	e.printStackTrace(printWriter);
				message = e.getMessage();
				message += e.getCause();
				message += "<br />";
				message += result;
				System.out.println("translation save failure:" + message);
			} 
			finally{
	//			cat.cleanUpStatements();
				if (connector != null){
					connector.close(connection);
				}
			}
		//String error = "An error occurred while saving the translations: " + message;
	
	}
}
